using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;
//using System.Collections.Generic;

namespace Raptor
{
  public partial class frmRaptorCDOPrms : Form
  {
    #region Class Variables
    int iSelectedTablesCount = 0;

    private string m_PrimaryKeyColName;

    private string m_PrimaryKeyType;

    private string m_ActiveColName;

    private string m_TableName;

    private bool m_Approved;

    SqlConnection SQLConn;

    string[] sExecutionPlan = new string[17];

    GenOptions SystemOptions = new GenOptions();

    AppSettings thisSettings = new AppSettings();

    private Dictionary<string, bool> m_DOOptions;

    private List<List<String>> m_EmbededObjectList = new List<List<String>>();

    DataTable sourceTables = null, sourceColumns = null;

    private List<List<String>> m_SelectedTableColumns;

    private List<List<List<String>>> m_SelectedObjects = new List<List<List<String>>>();

    private Dictionary<string, Dictionary<Dictionary<string, bool>, List<List<List<List<String>>>>>> test = new Dictionary<string, Dictionary<Dictionary<string, bool>, List<List<List<List<string>>>>>>();

    private Dictionary<Dictionary<string, bool>, List<List<List<List<String>>>>> test2 = new Dictionary<Dictionary<string, bool>, List<List<List<List<string>>>>>();
    
    private List<List<List<List<String>>>> test3 = new List<List<List<List<string>>>>();

    #endregion

    #region Properties

    public Dictionary<string,bool> DOOptions
    {
      get { return m_DOOptions; }
      set { m_DOOptions = value; }
    }
	
    
    public bool Approved
    {
      get { return m_Approved; }
      set { m_Approved = value; }
    }
	
    public List<List<String>> selectedTableColumns
    {
      get { return m_SelectedTableColumns; }
      set { m_SelectedTableColumns = value; }  
    }

    public List<List<String>> EmbededObjectList
    {
      get { return m_EmbededObjectList; }
      set { m_EmbededObjectList = value; }
    }

    #endregion

    #region Form Initialize & Load

    public frmRaptorCDOPrms()
    {
      InitializeComponent();
    }

    private void frmRaptor_Load(object sender, EventArgs e)
    {
      sExecutionPlan[0] = "DO";
      sExecutionPlan[1] = "SPC";
      sExecutionPlan[2] = "SPR";
      sExecutionPlan[3] = "SPU";
      sExecutionPlan[4] = "SPD";
      sExecutionPlan[5] = "SPRA";
      sExecutionPlan[6] = "CT";
      sExecutionPlan[7] = "EAL";
      sExecutionPlan[8] = "WS";
      sExecutionPlan[9] = "CDODO";
      sExecutionPlan[10] = "DOCOL";
      sExecutionPlan[11] = "DOPAR";
      sExecutionPlan[12] = "CTPAR";
      sExecutionPlan[13] = "EALPAR";
      sExecutionPlan[14] = "CDODOPAR";
      sExecutionPlan[15] = "CDOCOLPAR";
      sExecutionPlan[16] = "CDOTEST";

      LoadSettings();

      #region Test Code
      //txtTableName.Text = "DepositRefund";
      //btnLoadDataStructureClick(sender, e);
      //DGTableDetails.Rows[2].Cells[5].Value = true;
      //DGTableDetails.Rows[3].Cells[5].Value = true;
      //DGTableDetails.Rows[4].Cells[5].Value = true;
      //btnDOOptions_Click(sender, e);
      //btnDone_Click(sender, e);
      //cmbWebService.SelectedIndex = 1;
      //btnGenerate_Click(sender, e);
      #endregion
    }

    #endregion

    #region Support Methods

    #region DB Methods

    private void LoadDBTable()
    {
      string[] tableRestrictions = new string[4];
      int iTableCounter = 1, iMaxTables = 0;

      SetDBConn();
      // Setup the Connection string

      //connectionString = @"Data Source=" + ealMapSystemSource.MapSystemSourceData.DataSource + ";" +
      //                   @"Initial Catalog=" + ealMapSystemSource.MapSystemSourceData.InitialCatalog + ";" +
      //                   @"Workstation ID=WSDataMapping;";

      //if (ealMapSystemSource.MapSystemSourceData.IsIntegratedSecurity)
      //{
      //  connectionString += "Integrated Security=SSPI;";
      //}
      //else
      //{
      //  connectionString += @"User ID=" + ealMapSystemSource.MapSystemSourceData.UserId + ";" +
      //                      @"PWD=" + ealMapSystemSource.MapSystemSourceData.Password + ";";
      //}

      //// Create and setup the connection
      //sqlConnection = new SqlConnection();
      //sqlConnection.ConnectionString = connectionString;

      try
      {
        // Retrieve the Tables from the schema
        //sqlConnection.Open();
        sourceTables = SQLConn.GetSchema("Tables");
        iMaxTables = sourceTables.Rows.Count;
        progressBarTables.Maximum = iMaxTables;
        // Handle all the Tables returned
        this.Cursor = Cursors.WaitCursor;
        int iRowIndex = 0;
        foreach (DataRow currentTableRow in sourceTables.Rows)
        {
          // Setup the Table restrictions
          tableRestrictions[2] = currentTableRow["TABLE_NAME"].ToString();
          // Retrieve all the columns associated with the Table
          sourceColumns = SQLConn.GetSchema("Columns", tableRestrictions);
          // Handle all the Columns returned
          iRowIndex = GrdGenerate.Rows.Add(false, currentTableRow["TABLE_NAME"].ToString(), null, false, null);
          GrdGenerate.Rows[iRowIndex].Tag = sourceColumns;
          iTableCounter++;
          if (iTableCounter <= iMaxTables)
          {
            progressBarTables.Value = iTableCounter;
            lblTableCounter.Text = iTableCounter + " of " + iMaxTables;
            //GrdGenerate.Refresh();
            lblTableCounter.Refresh();
            progressBarTables.Refresh();
            //sourceColumns.Constraints
            //DataTable dt = SQLConn.GetSchema("ForeignKeys", tableRestrictions);
          }
          //foreach (DataRow currentColumnRow in sourceColumns.Rows)
          //{
            
          //currentColumnRow.co
          //  // Create and setup a new Map System Source Field data object
          //  //doSourceField = new DOMapSystemSourceField();
          //  //doSourceField.MapSystemSourceId = ealMapSystemSource.MapSystemSourceData.Id;
          //  //doSourceField.Table = currentTableRow["TABLE_NAME"].ToString();
          //  //doSourceField.Column = currentColumnRow["COLUMN_NAME"].ToString();
          //  //doSourceField.Type = currentColumnRow["DATA_TYPE"].ToString();
          //  //doSourceField.IsMandatory = currentColumnRow["IS_NULLABLE"].ToString() == "YES" ? false : true;
          //  //doSourceField.IsActive = true;

          //  // Add the object to the return values
          //  //returnValue.Add(doSourceField);
          //}
        }
        this.Cursor = Cursors.Default;
        lblTableListInfo.Text = iMaxTables.ToString() + " loaded ...";
        lblTableListInfo.Refresh();
      }
      catch (Exception runtimeException)
      {
        throw runtimeException;
      }
      finally
      {
        // Close the connection if required
        if (SQLConn.State == ConnectionState.Open) { SQLConn.Close();}
        GrdGenerate.Sort(GrdGenerate.Columns[1], ListSortDirection.Ascending);
      }


      //PUT SCHEMA CHANGES HERE

      //DataSet DS = new DataSet();
      //SqlDataAdapter SQA = new SqlDataAdapter("SELECT * FROM " + txtTableName.Text, SQLConn);

      //DGTableDetails.Rows.Clear();

//      try
//      {
      //  SQA.Fill(DS);

      //  txtTableName.Text = txtTableName.Text[0].ToString().ToUpper() + txtTableName.Text.Substring(1);

      //  bool isFirstColumn = true;

      //  foreach (DataColumn DC in DS.Tables[0].Columns)
      //  {
      //    string ctype = DC.DataType.ToString() == "System.Byte[]" ? "DateTime" : GetDataType(DC.DataType.ToString());
      //    string DCDataType = DC.DataType.ToString() == "System.Byte[]" ? "System.DateTime" : DC.DataType.ToString();
      //    string cfieldLength = "";
      //    if (ctype == "string") cfieldLength = "100";
      //    bool isRecordID = false;
      //    if (DC.Unique) isRecordID = true;

      //    if (isFirstColumn)
      //    {
      //      isFirstColumn = false;
      //      isRecordID = true;
      //    }

      //    DCDataType = ForceDataType(DCDataType);

      //    // Get the isnull property
      //    string[] tableRestrictions = new string[4];

      //    tableRestrictions[2] = txtTableName.Text;

      //    // Retrieve all the columns associated with the Table

      //    DataTable sourceColumns = SQLConn.GetSchema("Columns", tableRestrictions);

      //    bool isNullable = false;

      //    // Handle all the Columns returned

      //    foreach (DataRow currentColumnRow in sourceColumns.Rows)
      //    {
      //      if (currentColumnRow["COLUMN_NAME"].ToString() == DC.ColumnName)
      //      {
      //        isNullable = currentColumnRow["IS_NULLABLE"].ToString() == "YES" ? true : false;
      //        break;
      //      }
      //    }   

      //    //string colLength = DC

      //    DGTableDetails.Rows.Add(DC.ColumnName, DCDataType, ctype, cfieldLength, isRecordID, false, false, isNullable);
      //  }

  //    }
  //    catch (SqlException ex)
  //    {
  //      MessageBox.Show(ex.Message);
  //    }
    }

    private Boolean LoadStroredProcTable()
    {
      //SetDBConn();
      //DataSet DS = new DataSet();
      //SqlDataAdapter SQA = new SqlDataAdapter(txtSPName.Text, SQLConn);

      //DGTableDetails.Rows.Clear();

      //try
      //{
      //  SQA.Fill(DS);

      //  //txtTableName.Text = txtTableName.Text[0].ToString().ToUpper() + txtTableName.Text.Substring(1);

      //  bool isFirstColumn = true;

      //  foreach (DataColumn DC in DS.Tables[0].Columns)
      //  {
      //    string ctype = DC.DataType.ToString() == "System.Byte[]" ? "DateTime" : GetDataType(DC.DataType.ToString());
      //    string DCDataType = DC.DataType.ToString() == "System.Byte[]" ? "System.DateTime" : DC.DataType.ToString();
      //    string cfieldLength = "";
      //    if (ctype == "string") cfieldLength = "100";
      //    bool isRecordID = false;
      //    if (DC.Unique) isRecordID = true;

      //    if (isFirstColumn)
      //    {
      //      isFirstColumn = false;
      //      isRecordID = true;
      //    }

      //    DCDataType = ForceDataType(DCDataType);

      //    // Get the isnull property
      //    string[] tableRestrictions = new string[4];

      //    tableRestrictions[2] = txtSPName.Text;

      //    // Retrieve all the columns associated with the Table

      //    DataTable sourceColumns = SQLConn.GetSchema("Columns", tableRestrictions);

      //    bool isNullable = false;

      //    // Handle all the Columns returned

      //    foreach (DataRow currentColumnRow in sourceColumns.Rows)
      //    {
      //      if (currentColumnRow["COLUMN_NAME"].ToString() == DC.ColumnName)
      //      {
      //        isNullable = currentColumnRow["IS_NULLABLE"].ToString() == "YES" ? true : false;
      //        break;
      //      }
      //    }

      //    //string colLength = DC

      //    DGTableDetails.Rows.Add(DC.ColumnName, DCDataType, ctype, cfieldLength, isRecordID, false, false, isNullable);
      //  }
      //  return true; 
      //}
      //catch (SqlException ex)
      //{
      //  MessageBox.Show(ex.Message);
        return false;
      //}
    }

    private void SetDBConn()
    {
      string sConnectionString = "Data Source=" + txtDBServer.Text.Trim() + ";Initial Catalog=" + txtDBName.Text + ";";
      if (chkWindowsAuth.Checked)
        sConnectionString += "Integrated Security=SSPI;";
      else
        sConnectionString += "User ID=" + txtDBUserID.Text.Trim() + ";Password=" + txtDBPassword.Text.Trim() + ";";

      SQLConn = new SqlConnection(sConnectionString);
      SQLConn.Open();
    }

    #region DataType Stuff

    private string ForceDataType(string DataType)
    {
      string sReturn = DataType;
      switch (DataType)
      {
        case "System.Int16":
          {
            sReturn = "System.Int32";
            break;
          }
        case "System.Int64":
          {
            sReturn = "System.Int32";
            break;
          }
      }
      return (sReturn);
    }

    private string GetDataType(string DataType)
    {
      string sReturn = "";
      switch (DataType)
      {
        case "System.Int16":
          {
            sReturn = "int";
            break;
          }
        case "System.Int32":
          {
            sReturn = "long";
            break;
          }
        case "System.Int64":
          {
            sReturn = "long";
            break;
          }
        case "System.String":
          {
            sReturn = "string";
            break;
          }
        case "System.Boolean":
          {
            sReturn = "bool";
            break;
          }
        case "System.Byte[]":	// System.TimeStamp does not exist - returned as a Byte[]... Trevisen
          {
						sReturn = "DateTime";
            break;
          }
        case "System.DateTime":
          {
            sReturn = "DateTime";
            break;
          }
        case "System.Decimal":
          {
            sReturn = "decimal";
            break;
          }
        case "System.Double":
          {
            sReturn = "decimal";
            break;
          }
        case "System.Xml":
          {
            sReturn = "xml";
            break;
          }
        default:
          {
            throw new Exception("GetDataType: Unhandled Data Type " + DataType);
          }
      }
      return sReturn;
    }

    private string GetSystemDataType(string DataType)
    {
      string sReturn = "";
      switch (DataType)
      {
        case "System.Int16":
          {
            sReturn = "DbType.Int32";
            break;
          }
        case "System.Int32":
          {
            sReturn = "DbType.Int32";
            break;
          }
        case "System.Int64":
          {
            sReturn = "DbType.Int32";
            break;
          }
        case "System.String":
          {
            sReturn = "DbType.String";
            break;
          }
        case "System.Boolean":
          {
            sReturn = "DbType.Boolean";
            break;
          }
        case "System.Byte[]":	// System.TimeStamp does not exist - returned as a Byte[]... Trevisen
          {
            sReturn = "DbType.DateTime";
            break;
          }
        case "System.DateTime":
          {
            sReturn = "DbType.DateTime";
            break;
          }
        case "System.Decimal":
          {
            sReturn = "DbType.Decimal";
            break;
          }
        case "System.Double":
          {
            sReturn = "DbType.Decimal";
            break;
          }
        case "System.Xml":
          {
            sReturn = "DbType.Xml";
            break;
          }
        default:
          {
            throw new Exception("GetDataType: Unhandled Data Type " + DataType);
          }
      }
      return sReturn;
    }

    private string GetDataTypeConverter(string DataType)
    {
      string sReturn = "";
      switch (DataType)
      {
        case "System.Int16":
          {
            sReturn = "Convert.ToInt32";
            break;
          }
        case "System.Int32":
          {
            sReturn = "Convert.ToInt32";
            break;
          }
        case "System.Int64":
          {
            sReturn = "Convert.ToInt32";
            break;
          }
        case "System.String":
          {
            sReturn = "";
            break;
          }
        case "System.Boolean":
          {
            sReturn = "Convert.ToBoolean";
            break;
					}
				case "System.Byte[]":	// System.TimeStamp does not exist - returned as a Byte[]
					{
            sReturn = "Convert.ToDateTime";
            break;
          }
        case "System.DateTime":
          {
            sReturn = "Convert.ToDateTime";
            break;
          }
        case "System.Decimal":
          {
            sReturn = "Convert.ToDecimal";
            break;
          }
        case "System.Double":
          {
            sReturn = "Convert.ToDecimal";
            break;
          }
        case "System.Xml":
          {
            sReturn = "";
            break;
          }
        default:
          {
            throw new Exception("GetDataType: Unhandled Data Type " + DataType);
          }
      }
      return sReturn;
    }

    private string GetDBDataType(string DataType, string length)
    {
      string sReturn = "";
      switch (DataType)
      {
        case "System.Int16":
        case "System.Int32":
          {
            sReturn = "bigint";
            break;
          }
        case "System.Int64":
          {
            sReturn = "bigint";
            break;
          }
        case "System.String":
          {
            sReturn = "varchar(" + length + ")";
            break;
          }
        case "System.Boolean":
          {
            sReturn = "bit";
            break;
					}
				case "System.Byte[]":	// System.TimeStamp does not exist - returned as a Byte[]
          {
						sReturn = "DateTime";
            break;
          }
        case "System.DateTime":
          {
            sReturn = "DateTime";
            break;
          }
        case "System.Decimal":
          {
            sReturn = "decimal";
            break;
          }
        case "System.Double":
          {
            sReturn = "decimal";
            break;
          }
        case "System.Xml":
          {
            sReturn = "xml";
            break;
          }
        default:
          {
            throw new Exception("Unhandled GetDBType Type: " + DataType);
          }
      }
      return sReturn;
    }

    private string ClearDataType(string DataType)
    {
      string sReturn = "";
      switch (DataType)
      {
        case "System.Int16":
          {
            sReturn = "0";
            break;
          }
        case "System.Int32":
          {
            sReturn = "0";
            break;
          }
        case "System.Int64":
          {
            sReturn = "0";
            break;
          }
        case "System.String":
          {
            sReturn = "\"\"";
            break;
          }
        case "System.Boolean":
          {
            sReturn = "false";
            break;
          }
        case "System.Decimal":
          {
            sReturn = "0";
            break;
          }
        case "System.Double":
          {
            sReturn = "0";
            break;
          }
        case "System.DateTime":
          {
            sReturn = "new DateTime(1000, 1, 1)";
            break;
          }
        case "System.Xml":
          {
            sReturn = "\"\"";
            break;
          }
        default:
          {
            throw new Exception("Unhandled ClearDataType Type: " + DataType);
          }
      }
      return sReturn;
    }

    #endregion

    #endregion

    #region Generation Methods
    
    #region TemplateMethods

    private string GetTemplateText(string templateName)
    {
      string spText = "";

      #region Get the Template

      using (StreamReader sr = new StreamReader(Application.StartupPath + @"\Templates\" + templateName))
      {
        string line;
        while ((line = sr.ReadLine()) != null)
        {
          line = line + "\n\r";
          spText = spText + line;
        }
      }

      #endregion

      return (spText);
    }

    #endregion

    #region StoredProcs Methods

    private void CreateStoredProcs()
    {
      CreateSPCreate();
      CreateSPDelete();
      CreateSPRetrieve();
      CreateSPUpdate();
      CreateSPRetreiveAll();
    }

    private void CreateSPDelete()
    {
      // Ensure the Table has a Column for inactive

      string spText = "";

      if (m_ActiveColName == "")
        spText = GetTemplateText("spDeleteAllTemplate.txt");
      else
        spText = GetTemplateText("spDeleteTemplate.txt");

      spText = spText.Replace("|TABLENAME|", m_TableName);
      spText = spText.Replace("|PRIMARYKEY|", m_PrimaryKeyColName);
      spText = spText.Replace("|PRIMARYKEYTYPE|", m_PrimaryKeyType);
      spText = spText.Replace("|ACTIVECOLNAME|", m_ActiveColName);
      spText = spText.Replace("|DATE|", DateTime.Now.ToShortDateString());

      txtSPDelete.Text = spText;
    }

    private void CreateSPRetrieve()
    {
      //string spText = "";

      //spText = GetTemplateText("spRetrieveTemplate.txt");

      //spText = spText.Replace("|TABLENAME|", txtTableName.Text.Trim());
      //spText = spText.Replace("|DATE|", DateTime.Now.ToShortDateString());

      //txtSPRetrieve.Text = spText;
    }

    #region Create Methods

    private void CreateSPCreate()
    {
      string spText = "";

      spText = GetTemplateText("spCreateTemplate.txt");

      string paramList = GetParameterList(false);
      string insertCols = GetInsertColNameList();
      string insertParams = GetInsertParamList();

      spText = spText.Replace("|SPPARAMTERES|", paramList);
      spText = spText.Replace("|TABLECOLUMNS|", insertCols);
      spText = spText.Replace("|PARAMSLIST|", insertParams);

      //spText = spText.Replace("|TABLENAME|", txtTableName.Text.Trim());

      spText = spText.Replace("|DATE|", DateTime.Now.ToShortDateString());

      txtSPCreate.Text = spText;
    }

    private string GetInsertColNameList()
    {
      string returnString = "";

      foreach (DataGridViewRow thisRow in DGTableDetails.Rows)
      {
        if (thisRow.Cells[0].Value != null)
        {
          if (Convert.ToBoolean(thisRow.Cells[4].Value))
          {
            continue;
          }

          if (thisRow.Cells[0].Value.ToString() == m_ActiveColName)
          {
            continue;
          }

          if (returnString != "")
          {
            returnString = returnString + ",\n\r\t\t\t";
          }
          returnString = returnString + thisRow.Cells[0].Value.ToString();

        }
      }

      return (returnString);
    }

    private string GetInsertParamList()
    {
      string returnString = "";

      foreach (DataGridViewRow thisRow in DGTableDetails.Rows)
      {
        if (thisRow.Cells[0].Value != null)
        {
          if (Convert.ToBoolean(thisRow.Cells[4].Value))
          {
            continue;
          }

          if (thisRow.Cells[0].Value.ToString() == m_ActiveColName)
          {
            continue;
          }

          if (returnString != "")
          {
            returnString = returnString + ",\n\r\t\t\t";
          }
          returnString = returnString + "@" + thisRow.Cells[0].Value.ToString();

        }
      }

      return (returnString);
    }

    #endregion

    #region UpdateMethods

    private void CreateSPUpdate()
    {
      string spText = "";

      spText = GetTemplateText("spUpdateTemplate.txt");

      string paramList = GetParameterList(true);
      string setList = GetUpdateSetList();

      spText = spText.Replace("|SPPARAMTERES|", paramList);
      spText = spText.Replace("|SETLIST|", setList);
      //spText = spText.Replace("|TABLENAME|", txtTableName.Text.Trim());
      spText = spText.Replace("|PRIMARYKEY|", m_PrimaryKeyColName);
      spText = spText.Replace("|PRIMARYKEYTYPE|", m_PrimaryKeyType);

      spText = spText.Replace("|DATE|", DateTime.Now.ToShortDateString());

      txtSPUpdate.Text = spText;
    }

    private string GetUpdateSetList()
    {
      string returnString = "";

      foreach (DataGridViewRow thisRow in DGTableDetails.Rows)
      {
        if (thisRow.Cells[0].Value != null)
        {
          if (!Convert.ToBoolean(thisRow.Cells[4].Value))
          {
            string sColName = thisRow.Cells[0].Value.ToString();

            //if (sColName.ToUpper() != txtTableName.Text.Trim().ToUpper() + "_ACTIVE")
            //{
            //  if (returnString != "")
            //    returnString = returnString + ", \n\r\t\t";

            //  returnString = returnString + sColName + " = @" + sColName;
            //}
          }
        }
      }

      return (returnString);
    }

    #endregion

    #region RetrieveAll Methods

    private void CreateSPRetreiveAll()
    {
      if (!chkDynamicQueries.Checked)
      {
        CreateSPRetreiveAllNonDynamic();
        return;
      }
      string spText = "";

      spText = GetTemplateText("spRetrieveAllTemplate.txt");

      string spParams = GetRetieveAllSPParams();
      string whereClause = GetRetrieveAllWhereStatement();

      if (chkLimit.Checked)
      {
        string limit = GetTemplateText("spMaxRecordsTemplate.txt");
        spText = spText.Replace("|MAXRECORDS|", limit);
        spText = spText.Replace("|TOP|", "TOP ' + CAST(@MaxResults as varchar) + '");
      }
      else
      {
        spText = spText.Replace("|MAXRECORDS|", "");
        spText = spText.Replace("|TOP|", "");
      }

      spText = spText.Replace("|SPPARAMTERES|", spParams);
      spText = spText.Replace("|WHERELIST|", whereClause);


      //spText = spText.Replace("|TABLENAME|", txtTableName.Text.Trim());
      spText = spText.Replace("|DATE|", DateTime.Now.ToShortDateString());

      txtSPRetrieveAll.Text = spText;
    }

    private void CreateSPRetreiveAllNonDynamic()
    {
      string spText = "";

      spText = GetTemplateText("spRetrieveAllNonDynamicTemplate.txt");

      string spParams = GetRetieveAllSPParams();
      string whereClause = GetRetrieveAllWhereStatement();

      spText = spText.Replace("|SPPARAMTERES|", spParams);
      spText = spText.Replace("|WHERELIST|", whereClause);

      if (chkLimit.Checked)
      {
        string limit = GetTemplateText("spMaxRecordsTemplate.txt");
        spText = spText.Replace("|MAXRECORDS|", limit);
        spText = spText.Replace("|MAXROWCOUNT|", "SET ROWCOUNT @MaxResults");
        spText = spText.Replace("|ROWCOUNTRESET|", "SET ROWCOUNT 0");
      }
      else
      {
        spText = spText.Replace("|MAXRECORDS|", "");
        spText = spText.Replace("|MAXROWCOUNT|", "");
        spText = spText.Replace("|ROWCOUNTRESET|", "");
      }

      //spText = spText.Replace("|TABLENAME|", txtTableName.Text.Trim());
      spText = spText.Replace("|DATE|", DateTime.Now.ToShortDateString());

      txtSPRetrieveAll.Text = spText;
    }

    private string GetRetieveAllSPParams()
    {
      string returnString = "";

      foreach (DataGridViewRow thisRow in DGTableDetails.Rows)
      {
        if (Convert.ToBoolean(thisRow.Cells[5].Value))
        {
          string sColName = thisRow.Cells[0].Value.ToString();

          //if (sColName.ToUpper() != txtTableName.Text.Trim().ToUpper() + "_ACTIVE")
          //{
          //  string sDBType = thisRow.Cells[1].Value.ToString();
          //  string slength = thisRow.Cells[3].Value.ToString();
          //  string sParamType = GetDBDataType(sDBType, slength);
          //  if (sParamType.ToLower() == "bit") sParamType = "numeric";

          //  // Exclude Active
          //  if (sColName.ToUpper() == txtTableName.Text.Trim().ToUpper() + "_ACTIVE")
          //    continue;

            if (returnString != "")
              returnString = returnString + ", \n\r\t\t";

            //returnString = returnString + "@" + sColName + " " + sParamType + " = null";
          //}
        }
      }

      return returnString;
    }    

    private string GetRetrieveAllWhereStatement()
    {
      string returnString = "";

      foreach (DataGridViewRow thisRow in DGTableDetails.Rows)
      {
        if (Convert.ToBoolean(thisRow.Cells[5].Value))
        {
          string sColName = thisRow.Cells[0].Value.ToString();

          string whereText = "";

          if (chkDynamicQueries.Checked)
          {
            // Data Type to determine wheter casting is necessarry.
            bool mustCast = false;
            string dataType = thisRow.Cells[2].Value.ToString();
            if (dataType.ToLower() == "long" || dataType.ToLower() == "int" || dataType.ToLower() == "decimal")
            {
              mustCast = true;
            }

            if (Convert.ToBoolean(thisRow.Cells[6].Value))
            {
              whereText = whereText + "\n\r\t";
              whereText = whereText + "IF @|FIELDNAME| IS NOT NULL";
              whereText = whereText + "\n\r\t BEGIN";
              whereText = whereText + "\n\r\t\t";
              if (mustCast)
              {
                whereText = whereText + "SET @Where = @Where + 'AND |TABLENAME|.|FIELDNAME| like ''%' + CAST(@|FIELDNAME| AS VARCHAR) + '%'' '";
              }
              else
              {
                whereText = whereText + "SET @Where = @Where + 'AND |TABLENAME|.|FIELDNAME| like ''%' + @|FIELDNAME| + '%'' '";
              }
              whereText = whereText + "\n\r\t END";
              
              //whereText = GetTemplateText("RetrieveAllLikeItemTemplate.txt");
            }
            else
            {
              whereText = whereText + "\n\r\t";
              whereText = whereText + "IF @|FIELDNAME| IS NOT NULL";
              whereText = whereText + "\n\r\t BEGIN";
              whereText = whereText + "\n\r\t\t";
              if (mustCast)
              {
                whereText = whereText + "SET @Where = @Where + 'AND |TABLENAME|.|FIELDNAME| = ' + CAST(@|FIELDNAME| AS VARCHAR) + ' '    ";
              }
              else
              {
                whereText = whereText + "SET @Where = @Where + 'AND |TABLENAME|.|FIELDNAME| = ' + @|FIELDNAME| + ' '    ";
              }
              whereText = whereText + "\n\r\t END";
              //whereText = GetTemplateText("RetrieveAllItemTemplate.txt");
            }
          }
          else
          {
            
            if (Convert.ToBoolean(thisRow.Cells[6].Value))
            {   
              whereText = "|TABLENAME|.|FIELDNAME| like ISNULL('%' + @|FIELDNAME| + '%',|TABLENAME|.|FIELDNAME|)";
            }
            else
            {              
              whereText = "|TABLENAME|.|FIELDNAME| = ISNULL(@|FIELDNAME|,|TABLENAME|.|FIELDNAME|)";              
            }
          }         
          
          whereText = whereText.Replace("|FIELDNAME|", sColName);

          if (!chkDynamicQueries.Checked)
          {
            if (returnString != "")
            {
              returnString = returnString + "\n\r\t\t  AND ";
            }
          }
          else
          {
            if (returnString != "")
            {
              returnString = returnString + "\n\r\t\t  ";
            }
          }

          returnString = returnString + whereText;
        }
      }


      return (returnString);
    }

    #endregion

    #region All Support methods

    private string GetParameterList(bool includeID)
    {
      //string returnString = "";

      //foreach (DataGridViewRow thisRow in DGTableDetails.Rows)
      //{
      //  if (thisRow.Cells[0].Value != null)
      //  {
      //    string sColName = thisRow.Cells[0].Value.ToString();

      //    if (sColName.ToUpper() != txtTableName.Text.Trim().ToUpper() + "_ACTIVE")
      //    {
      //      string sDBType = thisRow.Cells[1].Value.ToString();
      //      string slength = thisRow.Cells[3].Value.ToString();
      //      string sParamType = GetDBDataType(sDBType, slength);

      //      // Exclude Active
      //      if (sColName.ToUpper() == txtTableName.Text.Trim().ToUpper() + "_ACTIVE")
      //        continue;

      //      if (Convert.ToBoolean(thisRow.Cells[4].Value))
      //      {
      //        if (!includeID)
      //          continue;
      //      }

      //      if (returnString != "")
      //        returnString = returnString + ", \n\r\t\t";

      //      string defaultNull = "";
      //      if (Convert.ToBoolean(thisRow.Cells[7].Value))
      //        defaultNull = " = null";

      //      returnString = returnString + "@" + sColName + " " + sParamType + defaultNull;
      //    }

      //  }
      //}

      //return (returnString);
      return "";
    }

    private string GetFriendlyFieldName(string originalName)
    {
      string returnString;
      returnString = originalName.Replace(m_TableName + "_", "");
      // Ensure IDs are camel case
      if (returnString.Length > 2)
      {
        if ((returnString.Substring(returnString.Length - 3, 3) == "_ID") || (returnString.Substring(returnString.Length - 3, 3) == "_Id"))
          returnString = returnString.Substring(0, returnString.Length - 3) + "Id";
      }
      if (returnString == "ID") returnString = "Id";
      return (returnString);
    }

    #endregion

    #endregion

    #region DO Methods

    private void CreateDO()
    {
      string spText = "";

      spText = GetTemplateText("DOTemplate.txt");

      string constantList = GetConstantList();
      string privateVariableList = GetDOPrivateVariableList();
      string publicProperties = GetDOPropertyList();
      string clearList = GetDOClearVariableList();

      #region Set Namespace on DO

      spText = spText.Replace("|NAMESPACE|", txtNSDO.Text);

      #endregion

      #region Set Stored Proc Names depending on DO Selection

      string spCreate = "";
      //if (chkCreate.Checked)
        spCreate = "sp|TABLENAME|_Create";
      string spUpdate = "";
      //if (chkUpdate.Checked)
        spUpdate = "sp|TABLENAME|_Update";
      string spRetrieve = "";
      //if (chkRetrieve.Checked)
        spRetrieve = "sp|TABLENAME|_Retrieve";
      string spRetrieveAll = "";
      //if (chkRetrieveAll.Checked)
        spRetrieveAll = "sp|TABLENAME|_RetrieveAll";
      string spDelete = "";
      //if (chkDelete.Checked)
        spDelete = "sp|TABLENAME|_Delete";

      spText = spText.Replace("|SPCREATE|", spCreate);
      spText = spText.Replace("|SPDELETE|", spDelete);
      spText = spText.Replace("|SPRETRIEVE|", spRetrieve);
      spText = spText.Replace("|SPRETRIEVEALL|", spRetrieveAll);
      spText = spText.Replace("|SPUPDATE|", spUpdate);

      #endregion

      #region Set Can options on DO

      //spText = spText.Replace("|CANCREATE|", chkCreate.Checked.ToString().ToLower());
      //spText = spText.Replace("|CANUPDATE|", chkUpdate.Checked.ToString().ToLower());
      //spText = spText.Replace("|CANRETRIEVEALL|", chkRetrieveAll.Checked.ToString().ToLower());
      //spText = spText.Replace("|CANDELETE|", chkDelete.Checked.ToString().ToLower());

      #endregion

      spText = spText.Replace("|CONSTANTLIST|", constantList);
      spText = spText.Replace("|PRIVATEVARIABLELIST|", privateVariableList);
      spText = spText.Replace("|PUBLICPROPERTYLIST|", publicProperties);
      spText = spText.Replace("|CLEARVARIABLELIST|", clearList);
      spText = spText.Replace("|TABLENAME|", m_TableName);

      txtDO.Text = spText;

    }

    private string GetConstantList()
    {
      string returnString = "";

      foreach (DataGridViewRow thisRow in DGTableDetails.Rows)
      {
        if (thisRow.Cells[0].Value != null)
        {
          string columnName = thisRow.Cells[0].Value.ToString().Trim();
          string friendlyFieldName = GetFriendlyFieldName(columnName);

          string constTemplate = GetTemplateText("DOConstItem.txt");
          constTemplate = constTemplate.Replace("|FIELDFRIENDLYNAME|", friendlyFieldName);
          constTemplate = constTemplate.Replace("|UPPERFIELDFRIENDLYNAME|", friendlyFieldName.ToUpper());
          constTemplate = constTemplate.Replace("|FIELDNAME|", columnName);

          returnString = returnString + constTemplate;

        }
      }

      return (returnString);
    }

    private string GetDOPrivateVariableList()
    {
      string returnString = "";

      foreach (DataGridViewRow thisRow in DGTableDetails.Rows)
      {
        if (thisRow.Cells[0].Value != null)
        {
          string columnName = thisRow.Cells[0].Value.ToString().Trim();
          string friendlyFieldName = GetFriendlyFieldName(columnName);
          string fieldType = thisRow.Cells[2].Value.ToString().Trim();

          string line = "\t\tprivate " + fieldType + " m_" + friendlyFieldName + ";\n\r";

          returnString = returnString + line;

        }
      }

      return (returnString);
    }

    private string GetDOPropertyList()
    {
      string returnString = "";

      foreach (DataGridViewRow thisRow in DGTableDetails.Rows)
      {
        if (thisRow.Cells[0].Value != null)
        {
          string propertyTemplate = "";
          if (Convert.ToBoolean(thisRow.Cells[4].Value))
            propertyTemplate = GetTemplateText("DOPublicIDProperty.txt");
          else
            propertyTemplate = GetTemplateText("DOPublicProperty.txt");

          string dbType = GetSystemDataType(thisRow.Cells[1].Value.ToString().Trim());
          //dbType = GetSystemDataType(dbType);//dbType.Replace("System", "DbType");
          string friendlyFieldName = GetFriendlyFieldName(thisRow.Cells[0].Value.ToString().Trim());
          string fieldType = thisRow.Cells[2].Value.ToString().Trim();

          string deleteatt = "";
          //if (chkDelete.Checked)
          //{
          //  deleteatt = "\n\r\t\t[DataDelete(true)]";
          //}

          string smandatory = "true";
          if (Convert.ToBoolean(thisRow.Cells[7].Value) || thisRow.Cells[0].Value.ToString().IndexOf("Active") > 0)
          {
            smandatory = "false";
          }


          propertyTemplate = propertyTemplate.Replace("|FIELDFRIENDLYNAME|", friendlyFieldName);
          propertyTemplate = propertyTemplate.Replace("|UPPERFIELDFRIENDLYNAME|", friendlyFieldName.ToUpper());
          propertyTemplate = propertyTemplate.Replace("|DBTYPE|", dbType);
          propertyTemplate = propertyTemplate.Replace("|FIELDTYPE|", fieldType);

          propertyTemplate = propertyTemplate.Replace("|MANDATORY|", smandatory);
          propertyTemplate = propertyTemplate.Replace("|DELETEATTRIBUTE|", deleteatt);

          returnString = returnString + propertyTemplate + "\n\r";
        }
      }

      return (returnString);
    }

    private string GetDOClearVariableList()
    {
      string returnString = "";

      foreach (DataGridViewRow thisRow in DGTableDetails.Rows)
      {
        if (thisRow.Cells[0].Value != null)
        {
          string dbType = thisRow.Cells[1].Value.ToString().Trim();
          string friendlyFieldName = GetFriendlyFieldName(thisRow.Cells[0].Value.ToString().Trim());
          string fieldType = thisRow.Cells[2].Value.ToString().Trim();
          string val = ClearDataType(dbType);

          string line = "\t\t\tthis.m_" + friendlyFieldName + " = " + val + ";\n\r";

          returnString = returnString + line;
        }
      }

      return (returnString);
    }

    #endregion

    #region Contract Methods

    private void CreateContract()
    {
      string spText = "";

      spText = GetTemplateText("CTTemplate.txt");

      #region Set Namespace on CT

      //spText = spText.Replace("|NAMESPACE|", txtNSCT.Text);

      #endregion

      spText = spText.Replace("|TABLENAME|", m_TableName);

      //txtContract.Text = spText;
    }

    #endregion

    #region EAL Methods

    private void CreateEAL()
    {
      //string spText = "";

      //spText = GetTemplateText("EalTemplate.txt");

      //#region Set Namespace on EAL

      //spText = spText.Replace("|NAMESPACE|", txtNSEAL.Text);

      //#endregion

      //spText = spText.Replace("|TABLENAME|", m_TableName);

      //// Check if there are any retrieve all items
      //bool hasRetrieveAllParams = false;
      //foreach (DataGridViewRow thisRow in DGTableDetails.Rows)
      //{
      //  if (thisRow.Cells[0].Value != null)
      //  {
      //    if (Convert.ToBoolean(thisRow.Cells[5].Value))
      //    {
      //      hasRetrieveAllParams = true;
      //      break;
      //    }
      //  }
      //}

      //if (hasRetrieveAllParams)
      //{
      //  CreateExendedEAL();
      //  SystemOptions.GenPartialEAL = true;
      //  spText = spText.Replace("|PARTIAL|", "partial");
      //}
      //else
      //{
      //  spText = spText.Replace("|PARTIAL|", "");
      //}

      //txtEalBase.Text = spText;
    }

    private void CreateExendedEAL()
    {
      //string spText = "";

      //spText = GetTemplateText("EalExtendedTemplate.txt");

      //#region Set Namespace on EAL Extended

      //spText = spText.Replace("|NAMESPACE|", txtNSEAL.Text);

      //#endregion

      //string paramstring = GetEALRetrieveAllParams();
      //string paramAddList = GetEALRetrieveAllItems();

      //spText = spText.Replace("|PARAMETERLIST|", paramstring);
      //spText = spText.Replace("|RETRIEVEALLLIST|", paramAddList);
      //spText = spText.Replace("|TABLENAME|", m_TableName);

      //txtEalExtended.Text = spText;
    }

    private string GetEALRetrieveAllParams()
    {
      //string returnString = "";

      //foreach (DataGridViewRow thisRow in DGTableDetails.Rows)
      //{
      //  if (thisRow.Cells[0].Value != null)
      //  {
      //    if (Convert.ToBoolean(thisRow.Cells[5].Value))
      //    {
      //      string fieldType = thisRow.Cells[2].Value.ToString();
      //      string fieldName = thisRow.Cells[0].Value.ToString();
      //      string friendlyName = GetFriendlyFieldName(fieldName);
      //      // Force the friendly field name first character to lowercase
      //      friendlyName = friendlyName[0].ToString().ToLower() + friendlyName.Substring(1);

      //      //add it to the return string 
      //      if (returnString != "")
      //        returnString = returnString + ", ";
      //      returnString = returnString + fieldType + " " + friendlyName;
      //    }
      //  }
      //}

      //return (returnString);
      return "";
    }

    private string GetEALRetrieveAllItems()
    {
      //string returnString = "";

      //foreach (DataGridViewRow thisRow in DGTableDetails.Rows)
      //{
      //  if (thisRow.Cells[0].Value != null)
      //  {
      //    if (Convert.ToBoolean(thisRow.Cells[5].Value))
      //    {
      //      string itemTemplate = GetTemplateText("EalRetrieveAllItemTemplate.txt");

      //      string dbType = GetSystemDataType(thisRow.Cells[1].Value.ToString());
      //      //dbType = dbType.Replace("System", "DbType");
      //      string fieldName = thisRow.Cells[0].Value.ToString();
      //      string friendlyName = GetFriendlyFieldName(fieldName);
      //      string clearValue = ClearDataType(thisRow.Cells[1].Value.ToString().Trim());

      //      // Force the friendly field name first character to lowercase
      //      friendlyName = friendlyName[0].ToString().ToLower() + friendlyName.Substring(1);

      //      itemTemplate = itemTemplate.Replace("|FRIENDLYFIELDNAME|", friendlyName);
      //      itemTemplate = itemTemplate.Replace("|UPPERFRIENDLYFIELDNAME|", friendlyName.ToUpper());
      //      itemTemplate = itemTemplate.Replace("|CLEARVALUE|", clearValue);
      //      itemTemplate = itemTemplate.Replace("|FIELDNAME|", fieldName);
      //      itemTemplate = itemTemplate.Replace("|DBTYPE|", dbType);
      //      itemTemplate = itemTemplate + "\n\r\n\r";

      //      returnString = returnString + itemTemplate;


      //    }
      //  }
      //}

      //return (returnString);
      return "";
    }
    #endregion

    //#region Webservice Methods

    //private void CreateWebService()
    //{
    //  string spText = "";

    //  spText = GetTemplateText("WebServiceTemplate.txt");

    //  string retrievAllParamList = GetEALRetrieveAllParams();
    //  string callParameterList = GetWSParameterCallList();
    //  string valueSetList = GetWSValueSetList();
    //  string lowerTableName = m_TableName[0].ToString().ToLower() + m_TableName.Substring(1);

    //  #region Set Contract Methods

    //  string scontractCreate = "//Functionality not implemented on DO";
    //  string scontractDelete = "//Functionality not implemented on DO";
    //  string scontractUpdate = "//Functionality not implemented on DO";
    //  string scontractRetrieve = "//Functionality not implemented on DO";

    //  if (chkCreate.Checked)
    //    scontractCreate = GetWSContractMethodTemplate("Create");
    //  if (chkRetrieve.Checked)
    //    scontractRetrieve = GetWSContractMethodTemplate("Retrieve");
    //  if (chkDelete.Checked)
    //    scontractDelete = GetWSContractMethodTemplate("Delete");
    //  if (chkUpdate.Checked)
    //    scontractUpdate = GetWSContractMethodTemplate("Update");

    //  spText = spText.Replace("|RETRIEVECONTRACTMETHOD|", scontractRetrieve);
    //  spText = spText.Replace("|CREATECONTRACTMETHOD|", scontractCreate);
    //  spText = spText.Replace("|UPDATECONTRACTMETHOD|", scontractUpdate);
    //  spText = spText.Replace("|DELETECONTRACTMETHOD|", scontractDelete);

    //  #endregion

    //  spText = spText.Replace("|PARAMETERLIST|", retrievAllParamList);
    //  spText = spText.Replace("|CALLPARAMTERS|", callParameterList);
    //  spText = spText.Replace("|VALUESETLIST|", valueSetList);
    //  spText = spText.Replace("|SECTION|", txtWebServiceSection.Text.Trim());
    //  spText = spText.Replace("|TABLENAME|", m_TableName);

    //  spText = spText.Replace("|LOWERTABLENAME|", lowerTableName);

    //  txtWebService.Text = spText;
    //}

    //private string GetWSValueSetList()
    //{
    //  string returnString = "";

    //  foreach (DataGridViewRow thisRow in DGTableDetails.Rows)
    //  {
    //    if (thisRow.Cells[0].Value != null)
    //    {
    //      string columnName = thisRow.Cells[0].Value.ToString();
    //      string fieldType = thisRow.Cells[2].Value.ToString();
    //      string friendlyFieldName = GetFriendlyFieldName(columnName);

    //      string fieldConverter = GetDataTypeConverter(thisRow.Cells[1].Value.ToString());

    //      string itemTemplate = "";

    //      if (Convert.ToBoolean(thisRow.Cells[7].Value))          
    //        itemTemplate = GetTemplateText("WebServiceRetrieveItemSetTemplate.txt");          
    //      else
    //        itemTemplate = GetTemplateText("WebServiceRetrieveItemSetNonNullTemplate.txt");

    //      itemTemplate = itemTemplate.Replace("|UPPERFRIENDLYFIELDNAME|", friendlyFieldName.ToUpper());
    //      itemTemplate = itemTemplate.Replace("|FRIENDLYFIELDNAME|", friendlyFieldName);
    //      itemTemplate = itemTemplate.Replace("|FIELDCONVERTER|", fieldConverter);
    //      if (fieldConverter == "")
    //      {
    //        itemTemplate = itemTemplate.Replace("|TOSTRING|", ".ToString()");
    //        itemTemplate = itemTemplate.Replace("|BRACKET|", "");
    //        itemTemplate = itemTemplate.Replace("|CLOSEBRACKET|", "");            
    //      }
    //      else
    //      {
    //        itemTemplate = itemTemplate.Replace("|TOSTRING|", "");
    //        itemTemplate = itemTemplate.Replace("|BRACKET|", "(");
    //        itemTemplate = itemTemplate.Replace("|CLOSEBRACKET|", ")");
    //      }

    //      itemTemplate = itemTemplate + "\n\r";
    //      returnString = returnString + itemTemplate;
    //    }
    //  }

    //  return (returnString);
    //}

    //private string GetWSParameterCallList()
    //{
    //  string returnString = "";

    //  foreach (DataGridViewRow thisRow in DGTableDetails.Rows)
    //  {
    //    if (thisRow.Cells[0].Value != null)
    //    {
    //      if (Convert.ToBoolean(thisRow.Cells[5].Value))
    //      {
    //        string friendlyName = GetFriendlyFieldName(thisRow.Cells[0].Value.ToString());
    //        // Force the friendly field name first character to lowercase
    //        friendlyName = friendlyName[0].ToString().ToLower() + friendlyName.Substring(1);

    //        //add it to the return string 
    //        if (returnString != "")
    //          returnString = returnString + ", ";
    //        returnString = returnString + friendlyName;
    //      }
    //    }
    //  }

    //  return (returnString);
    //}

    //private string GetWSContractMethodTemplate(string method)
    //{
    //  string returnValue = GetTemplateText("WebServiceContractMethodTemplate.txt");

    //  returnValue = returnValue.Replace("|METHOD|", method);

    //  return (returnValue);
    //}

    //#endregion

    //#region CDO Methods

    //private void CreateCDO()
    //{
    //  string spText = "";

    //  spText = GetTemplateText("CdoTemplate.txt");

    //  #region Set Namespace on CDO Data Objects

    //  spText = spText.Replace("|NAMESPACE|", txtNSCDODO.Text);

    //  #endregion

    //  string variableList = GetCdoPrivateVariableList();
    //  string propertylist = GetCdoPropertyList();
    //  string setVariableList = GetCDOSetVariableList();
    //  string clearVariableList = GetCDOClearVariableList();
    //  string setcontractNoID = GetCDOSetContractVariableList(false,true);
    //  string setcontract = GetCDOSetContractVariableList(true,false);

    //  if (chkEmbedObject.Checked)
    //  {
    //    //CreateExendedEAL();
    //    SystemOptions.GenPartialCDODO = true;
    //    spText = spText.Replace("|PARTIAL|", "partial");
    //  }
    //  else
    //  {
    //    spText = spText.Replace("|PARTIAL|", "");
    //  }

    //  spText = spText.Replace("|VARIABLELIST|", variableList);
    //  spText = spText.Replace("|PUBLICPROPERTYLIST|", propertylist);
    //  spText = spText.Replace("|SETVARIABLELIST|", setVariableList);
    //  spText = spText.Replace("|SETCONTRACTVARIABLELISTNOID|", setcontractNoID);
    //  spText = spText.Replace("|SETCONTRACTVARIABLELIST|", setcontract);
    //  spText = spText.Replace("|CLEARVARIABLELIST|", clearVariableList);
    //  spText = spText.Replace("|CWS|", cmbWebService.Text.Trim());
    //  spText = spText.Replace("|WEBSERVICENAME|", cmbWebService.Text.Trim().Replace("Cws", ""));

    //  spText = spText.Replace("|TABLENAME|", m_TableName);

    //  string sEmbededProperties = "";
    //  string sEmbededCDOProperties = "";
    //  string sEmbededCOLProperties = "";
    //  string sEmbededConstructors = "";
    //  //string sEmbededCDOConstructors = "";
    //  string sEmbededCOLConstructors = "";
    //  string sEmbededObjectsClear = "";
    //  string sEmbededCDOObjectsClear = "";
    //  string sEmbededCOLObjectsClear = "";
    //  string sObjectName = "";
    //  string sObjectRegion = "";

    //  if (SystemOptions.GenPartialCDODO)
    //  {

    //    //PUT CODE HERE TO EMBED OBJECT ACCORDING TO WHETER THAY ARE CDO OR COLLECTIONS
    //    spText += "\n\r\n\r\t#region Associated Objects and Collections\n\r";
    //    sEmbededCOLProperties = "\n\r\t#region " + txtTableName.Text + " Embeded Collections\n\r";
    //    sEmbededCDOProperties = "\n\r\t#region " + txtTableName.Text + " Embeded Objects\n\r";
    //    sEmbededCOLConstructors = "\n\r\n\r\t#region " + txtTableName.Text + " Embeded Contructors\n\r";
    //    sEmbededCDOObjectsClear = "\n\r\n\r\t#region Clear " + txtTableName.Text + " Embeded Objects\n\r";
    //    sEmbededCOLObjectsClear = "\n\r\t#region Clear " + txtTableName.Text + " Embeded Collections\n\r";

    //    foreach (string sEmbededObject in sEmbededObjectList)
    //    {
    //      sObjectName = sEmbededObject.Substring(0, sEmbededObject.IndexOf(","));
    //      sObjectRegion = sEmbededObject.Substring(sEmbededObject.IndexOf(",") + 1,sEmbededObject.Length - (sObjectName.Length + 1));
    //      switch (sObjectRegion)
    //      {
    //        case "CDO":
    //          {
    //            sEmbededCDOProperties += "\tprivate Cdo" + sObjectName + " m_" + sObjectName + ";\n\r";
    //            //sEmbededCDOConstructors += "\n\r\n\r\tthis.m_" + sObjectName + " = new Cdo" + sObjectName + "();";
    //            sEmbededCDOObjectsClear += "\tif(this.m_" + sObjectName + " != null) this.m_" + sObjectName + ".Clear();\n\r";
    //            break;
    //          }
    //        case "COL":
    //          {
    //            sEmbededCOLProperties += "\tprivate Cdo" + sObjectName + "Collection m_" + sObjectName + ";\n\r";
    //            sEmbededCOLConstructors += "\tthis.m_" + sObjectName + " = new Cdo" + sObjectName + "Collection();\n\r";
    //            sEmbededCOLObjectsClear += "\tthis.m_" + sObjectName + ".Clear();\n\r";
    //            break;
    //          }
    //      }
    //      //sEmbededProperties += "\tprivate Cdo" + sEmbededObject + "Collection m_" + sEmbededObject + ";\n\r";
    //      //sEmbededConstructors += "\n\r\n\r\tthis.m_" + sEmbededObject + " = new Cdo" + sEmbededObject + "Collection();";
    //      //sEmbededObjectsClear += "\n\r\n\r\tthis.m_" + sEmbededObject + ".Clear();";
    //    }
    //    sEmbededCOLProperties += "\t#endregion";
    //    sEmbededCDOProperties += "\t#endregion";
    //    sEmbededCOLConstructors += "\t#endregion";
    //    sEmbededCDOObjectsClear += "\t#endregion\n\r";
    //    sEmbededCOLObjectsClear += "\t#endregion";

    //    sEmbededProperties = sEmbededCDOProperties + "\n\r\n\r" + sEmbededCOLProperties;
    //    sEmbededConstructors = sEmbededCOLConstructors + "\n\r";
    //    sEmbededObjectsClear = sEmbededCDOObjectsClear + sEmbededCOLObjectsClear;

    //    spText = spText.Replace("|EMBEDCONSTUCTOR|", sEmbededConstructors);
    //    spText = spText.Replace("|EMBEDEDOBJECTCLEAR|", sEmbededObjectsClear);
    //    spText = spText.Replace("|EMBEDEDOBJECTPROPERTY|", sEmbededProperties);
    //    spText = spText.Replace("|EMBEDRETURNVALUE|", "SaveMyCollections()");
    //    //spText = spText.Replace("|EMBEDEDOBJECTNAME|", txtEmbededObjectName.Text);//this.m_JournalDetails.Clear();
    //    spText += "\t#endregion";
    //    CreateCDOExtended();
    //  }
    //  else
    //  {
    //    spText = spText.Replace("|EMBEDCONSTUCTOR|", "");
    //    spText = spText.Replace("|EMBEDEDOBJECTCLEAR|", "");
    //    spText = spText.Replace("|EMBEDEDOBJECTPROPERTY|", "");
    //    spText = spText.Replace("|EMBEDRETURNVALUE|", "true");
    //  }
    //  txtCDO.Text = spText;
    //}

    //private void CreateAuditCDO()
    //{
    //  string spText = "";

    //  spText = GetTemplateText("CdoAuditTemplate.txt");

    //  #region Set Namespace on CDO Data Objects

    //  spText = spText.Replace("|NAMESPACE|", txtNSCDODO.Text);

    //  #endregion

    //  string variableList = GetCdoPrivateVariableList();
    //  string propertylist = GetCdoPropertyList();
    //  string setVariableList = GetCDOSetVariableList();
    //  string clearVariableList = GetCDOClearVariableList();
    //  string setcontractNoID = GetCDOSetContractVariableList(false, true);
    //  string setcontract = GetCDOSetContractVariableList(true, false);

    //  if (chkEmbedObject.Checked)
    //  {
    //    //CreateExendedEAL();
    //    SystemOptions.GenPartialCDODO = true;
    //    spText = spText.Replace("|PARTIAL|", "partial");
    //  }
    //  else
    //  {
    //    spText = spText.Replace("|PARTIAL|", "");
    //  }

    //  spText = spText.Replace("|VARIABLELIST|", variableList);
    //  spText = spText.Replace("|PUBLICPROPERTYLIST|", propertylist);
    //  spText = spText.Replace("|SETVARIABLELIST|", setVariableList);
    //  spText = spText.Replace("|SETCONTRACTVARIABLELISTNOID|", setcontractNoID);
    //  spText = spText.Replace("|SETCONTRACTVARIABLELIST|", setcontract);
    //  spText = spText.Replace("|CLEARVARIABLELIST|", clearVariableList);
    //  spText = spText.Replace("|CWS|", cmbWebService.Text.Trim());
    //  spText = spText.Replace("|WEBSERVICENAME|", cmbWebService.Text.Trim().Replace("Cws", ""));
    //  spText = spText.Replace("|AUDITENTITYID|", txtEntityID.Text);

    //  spText = spText.Replace("|TABLENAME|", m_TableName);

    //  string sEmbededProperties = "";
    //  string sEmbededCDOProperties = "";
    //  string sEmbededCOLProperties = "";
    //  string sEmbededConstructors = "";
    //  //string sEmbededCDOConstructors = "";
    //  string sEmbededCOLConstructors = "";
    //  string sEmbededObjectsClear = "";
    //  string sEmbededCDOObjectsClear = "";
    //  string sEmbededCOLObjectsClear = "";
    //  string sObjectName = "";
    //  string sObjectRegion = "";

    //  if (SystemOptions.GenPartialCDODO)
    //  {

    //    //PUT CODE HERE TO EMBED OBJECT ACCORDING TO WHETER THAY ARE CDO OR COLLECTIONS
    //    spText += "\n\r\n\r\t#region Associated Objects and Collections\n\r";
    //    sEmbededCOLProperties = "\n\r\t#region " + txtTableName.Text + " Embeded Collections\n\r";
    //    sEmbededCDOProperties = "\n\r\t#region " + txtTableName.Text + " Embeded Objects\n\r";
    //    sEmbededCOLConstructors = "\n\r\n\r\t#region " + txtTableName.Text + " Embeded Contructors\n\r";
    //    sEmbededCDOObjectsClear = "\n\r\n\r\t#region Clear " + txtTableName.Text + " Embeded Objects\n\r";
    //    sEmbededCOLObjectsClear = "\n\r\t#region Clear " + txtTableName.Text + " Embeded Collections\n\r";

    //    foreach (string sEmbededObject in sEmbededObjectList)
    //    {
    //      sObjectName = sEmbededObject.Substring(0, sEmbededObject.IndexOf(","));
    //      sObjectRegion = sEmbededObject.Substring(sEmbededObject.IndexOf(",") + 1, sEmbededObject.Length - (sObjectName.Length + 1));
    //      switch (sObjectRegion)
    //      {
    //        case "CDO":
    //          {
    //            sEmbededCDOProperties += "\tprivate Cdo" + sObjectName + " m_" + sObjectName + ";\n\r";
    //            //sEmbededCDOConstructors += "\n\r\n\r\tthis.m_" + sObjectName + " = new Cdo" + sObjectName + "();";
    //            sEmbededCDOObjectsClear += "\tif(this.m_" + sObjectName + " != null) this.m_" + sObjectName + ".Clear();\n\r";
    //            break;
    //          }
    //        case "COL":
    //          {
    //            sEmbededCOLProperties += "\tprivate Cdo" + sObjectName + "Collection m_" + sObjectName + ";\n\r";
    //            sEmbededCOLConstructors += "\tthis.m_" + sObjectName + " = new Cdo" + sObjectName + "Collection();\n\r";
    //            sEmbededCOLObjectsClear += "\tthis.m_" + sObjectName + ".Clear();\n\r";
    //            break;
    //          }
    //      }
    //      //sEmbededProperties += "\tprivate Cdo" + sEmbededObject + "Collection m_" + sEmbededObject + ";\n\r";
    //      //sEmbededConstructors += "\n\r\n\r\tthis.m_" + sEmbededObject + " = new Cdo" + sEmbededObject + "Collection();";
    //      //sEmbededObjectsClear += "\n\r\n\r\tthis.m_" + sEmbededObject + ".Clear();";
    //    }
    //    sEmbededCOLProperties += "\t#endregion";
    //    sEmbededCDOProperties += "\t#endregion";
    //    sEmbededCOLConstructors += "\t#endregion";
    //    sEmbededCDOObjectsClear += "\t#endregion\n\r";
    //    sEmbededCOLObjectsClear += "\t#endregion";

    //    sEmbededProperties = sEmbededCDOProperties + "\n\r\n\r" + sEmbededCOLProperties;
    //    sEmbededConstructors = sEmbededCOLConstructors + "\n\r";
    //    sEmbededObjectsClear = sEmbededCDOObjectsClear + sEmbededCOLObjectsClear;

    //    spText = spText.Replace("|EMBEDCONSTUCTOR|", sEmbededConstructors);
    //    spText = spText.Replace("|EMBEDEDOBJECTCLEAR|", sEmbededObjectsClear);
    //    spText = spText.Replace("|EMBEDEDOBJECTPROPERTY|", sEmbededProperties);
    //    spText = spText.Replace("|EMBEDRETURNVALUE|", "SaveMyCollections()");
    //    //spText = spText.Replace("|EMBEDEDOBJECTNAME|", txtEmbededObjectName.Text);//this.m_JournalDetails.Clear();
    //    spText += "\t#endregion";
    //    CreateCDOExtended();
    //  }
    //  else
    //  {
    //    spText = spText.Replace("|EMBEDCONSTUCTOR|", "");
    //    spText = spText.Replace("|EMBEDEDOBJECTCLEAR|", "");
    //    spText = spText.Replace("|EMBEDEDOBJECTPROPERTY|", "");
    //    spText = spText.Replace("|EMBEDRETURNVALUE|", "true");
    //  }
    //  txtCDO.Text = spText;
    //}

    //private void CreateCDOExtended()
    //{
    //  string spText = "";
    //  string sExtendedVariableList = GetCDOExtendedSetProperties();
    //  string sExtendedMethods = GetCDOExtendedSetMethods();
    //  string sExtendedSaveMethods = GetCDOExtendedSetSaveMethods();

    //  spText = GetTemplateText("CdoExtendedTemplate.txt");

    //  #region Set Namespace on CDO Collections Extended

    //  spText = spText.Replace("|NAMESPACE|", txtNSCDODO.Text);

    //  #endregion

    //  sExtendedMethods = sExtendedMethods.Substring(0, sExtendedMethods.LastIndexOf("#endregion") - 1);
    //  sExtendedMethods += "\n\r\n\r|CDOEXTENDEDSAVEMETHODS|\n\r\n\r\t\t#endregion\n\r";
    //  spText = spText.Replace("|WEBSERVICENAME|", cmbWebService.Text.Trim().Replace("Cws", ""));
    //  spText = spText.Replace("|TABLENAME|", m_TableName);
    //  spText = spText.Replace("|CDOEXTENDEDVARIABLELIST|", sExtendedVariableList);
    //  spText = spText.Replace("|CDOEXTENDEDMETHODS|", sExtendedMethods);
      
    //  spText = spText.Replace("|CDOEXTENDEDSAVEMETHODS|", sExtendedSaveMethods);

      
    //  txtCdoDoExtended.Text = spText;
    //}

    //private string GetCDOExtendedSetProperties()
    //{
    //  string spText = "";
    //  string sObjectName = "";
    //  string sObjectRegion = "";

    //  foreach (string sEmbededObject in sEmbededObjectList)
    //  {
    //    sObjectName = sEmbededObject.Substring(0, sEmbededObject.IndexOf(","));
    //    sObjectRegion = sEmbededObject.Substring(sEmbededObject.IndexOf(",") + 1, sEmbededObject.Length - (sObjectName.Length + 1));
    //    spText += GetTemplateText("CdoExtendedVariableListTemplate.txt");

    //    spText = spText.Replace("|EMBEDEDOBJECTNAME|", sObjectName);
    //    spText = spText.Replace("|TABLENAME|", m_TableName);
    //  }

    //  return spText;
    //}

    //private string GetCDOExtendedSetMethods()
    //{
    //  string spText = "";
    //  string sObjectName = "";
    //  string sObjectRegion = "";

    //  foreach (string sEmbededObject in sEmbededObjectList)
    //  {
    //    sObjectName = sEmbededObject.Substring(0, sEmbededObject.IndexOf(","));
    //    sObjectRegion = sEmbededObject.Substring(sEmbededObject.IndexOf(",") + 1, sEmbededObject.Length - (sObjectName.Length + 1));
    //    spText += GetTemplateText("CdoExtendedMehodsTemplate.txt");

    //    spText = spText.Replace("|EMBEDEDOBJECTNAME|", sObjectName);
    //    spText = spText.Replace("|CWS|", cmbWebService.Text.Trim());

    //    spText = spText.Replace("|WEBSERVICENAME|", cmbWebService.Text.Trim().Replace("Cws", ""));

    //    spText = spText.Replace("|TABLENAME|", m_TableName);
    //  }

    //  return spText;
    //}

    //private string GetCDOExtendedSetSaveMethods()
    //{
    //  string spText = GetTemplateText("CdoExtendedMethodsSaveMethodTemplate.txt");
    //  string sTemp = "";
    //  string sObjectName = "";
    //  string sObjectRegion = "";

    //  foreach (string sEmbededObject in sEmbededObjectList)
    //  {
    //    sObjectName = sEmbededObject.Substring(0, sEmbededObject.IndexOf(","));
    //    sObjectRegion = sEmbededObject.Substring(sEmbededObject.IndexOf(",") + 1, sEmbededObject.Length - (sObjectName.Length + 1));
    //    sTemp += GetTemplateText("CdoExtendedMethodsSaveCollectionsTemplate.txt");

    //    sTemp = sTemp.Replace("|EMBEDEDOBJECTNAME|", sObjectName);
    //  }
    //  spText = spText.Replace("|EMBEDEDSAVEMETHODPROPERTIES|", sTemp);
    //  return spText;
    //}

    //private string GetCdoPrivateVariableList()
    //{
    //  string returnString = "";

    //  foreach (DataGridViewRow thisRow in DGTableDetails.Rows)
    //  {
    //    if (thisRow.Cells[0].Value != null)
    //    {
    //      // Do not create for the ID
    //      if (!Convert.ToBoolean(thisRow.Cells[4].Value))
    //      {
    //        string columnName = thisRow.Cells[0].Value.ToString().Trim();
    //        string friendlyFieldName = GetFriendlyFieldName(columnName);
    //        string fieldType = thisRow.Cells[2].Value.ToString().Trim();

    //        string line = "\t\tprivate " + fieldType + " m_" + friendlyFieldName + ";\n\r";

    //        returnString = returnString + line;
    //      }
    //    }
    //  }

    //  return (returnString);
    //}

    //private string GetCdoPropertyList()
    //{
    //  string returnString = "";

    //  foreach (DataGridViewRow thisRow in DGTableDetails.Rows)
    //  {
    //    if (thisRow.Cells[0].Value != null)
    //    {
    //      // Do not create for the ID
    //      if (!Convert.ToBoolean(thisRow.Cells[4].Value))
    //      {
    //        string propertyTemplate = GetTemplateText("CdoPropertyItem.txt");

    //        string columnName = thisRow.Cells[0].Value.ToString().Trim();
    //        string friendlyFieldName = GetFriendlyFieldName(columnName);
    //        string fieldType = GetSystemDataType(thisRow.Cells[1].Value.ToString().Trim());
    //        //fieldType = fieldType.Replace("System", "DbType");

    //        string mandatory = "";

    //        if (Convert.ToBoolean(thisRow.Cells[7].Value))
    //        {
    //          mandatory = "\n\r\t\t[CdoMandatory(false,false,false,false)]";
    //        }
    //        else
    //        {
    //          mandatory = "\n\r\t\t[CdoMandatory(false,true,true,false)]";
    //        }

    //        propertyTemplate = propertyTemplate.Replace("|MANDATORYITEM|", mandatory);

    //        propertyTemplate = propertyTemplate.Replace("|FRIENDLYNAME|", friendlyFieldName);
    //        propertyTemplate = propertyTemplate.Replace("|DBTYPE|", fieldType);
    //        propertyTemplate = propertyTemplate.Replace("|FIELDTYPE|", thisRow.Cells[2].Value.ToString().Trim());


    //        returnString = returnString + propertyTemplate + "\n\r";
    //      }
    //    }
    //  }

    //  return (returnString);
    //}

    //private string GetCDOSetVariableList()
    //{
    //  string returnString = "";

    //  foreach (DataGridViewRow thisRow in DGTableDetails.Rows)
    //  {
    //    if (thisRow.Cells[0].Value != null)
    //    {
    //      string columnName = thisRow.Cells[0].Value.ToString().Trim();
    //      string friendlyFieldName = GetFriendlyFieldName(columnName);
    //      string variableName = friendlyFieldName;
    //      if (variableName != "Id") variableName = "m_" + variableName;

    //      string line = "\t\t\t\t\t\tthis." + variableName + " = this.m_Contract." + m_TableName + "Data." + friendlyFieldName + ";";
    //      returnString = returnString + line + "\n\r";
    //    }
    //  }

    //  return (returnString);
    //}

    //private string GetCDOClearVariableList()
    //{
    //  string returnString = "";

    //  foreach (DataGridViewRow thisRow in DGTableDetails.Rows)
    //  {
    //    if (thisRow.Cells[0].Value != null)
    //    {
    //      string columnName = thisRow.Cells[0].Value.ToString().Trim();
    //      string friendlyFieldName = GetFriendlyFieldName(columnName);
    //      string fieldType = thisRow.Cells[1].Value.ToString().Trim();
    //      string clearValue = ClearDataType(fieldType);
    //      string variableName = friendlyFieldName;
    //      if (variableName != "Id") variableName = "m_" + variableName;

    //      string line = "\t\t\tthis." + variableName + " = " + clearValue + ";";
    //      returnString = returnString + line + "\n\r";
    //    }
    //  }

    //  return (returnString);
    //}

    //private string GetCDOSetContractVariableList(bool incluceID, bool bCreate)
    //{
    //  string returnString = "";

    //  foreach (DataGridViewRow thisRow in DGTableDetails.Rows)
    //  {
    //    if (thisRow.Cells[0].Value != null)
    //    {
    //      if ((!incluceID) && (Convert.ToBoolean(thisRow.Cells[4].Value)))
    //        continue;

    //      string columnName = thisRow.Cells[0].Value.ToString().Trim();
    //      string friendlyFieldName = GetFriendlyFieldName(columnName);
    //      string variableName = friendlyFieldName;
    //      if (variableName != "Id") variableName = "m_" + variableName;

    //      string line = "";
    //      if(bCreate)
    //        line = "\t\t\t\t\tthis.m_Contract." + m_TableName + "Data." + friendlyFieldName + " = this." + variableName + ";";
    //      else
    //        line = "\t\t\t\t\t\tthis.m_Contract." + m_TableName + "Data." + friendlyFieldName + " = this." + variableName + ";";

    //      returnString = returnString + line + "\n\r";
    //    }
    //  }

    //  return (returnString);
    //}

    //#region Collection Methods

    //private void CreateCDOCollection()
    //{
    //  string spText = "";

    //  spText = GetTemplateText("CdoCollectionTemplate.txt");

    //  #region Set Namespace on CDO Collections

    //  spText = spText.Replace("|NAMESPACE|", txtNSCDODO.Text);

    //  #endregion

    //  string lowerTableName = m_TableName[0].ToString().ToLower() + m_TableName.Substring(1);

    //  spText = spText.Replace("|LOWERTABLENAME|", lowerTableName);
    //  spText = spText.Replace("|TABLENAME|", m_TableName);
    //  spText = spText.Replace("|CWS|", cmbWebService.Text.Trim());
    //  spText = spText.Replace("|WEBSERVICENAME|", cmbWebService.Text.Trim().Replace("Cws", ""));

    //  txtCDOCollection.Text = spText;

    //  CreateCDOCollectionExtended();
    //  SystemOptions.GenPartialCDOCOL = true;
    //}

    //private void CreateCDOCollectionExtended()
    //{
    //  string spText = "";

    //  spText = GetTemplateText("CdoCollectionExtendedTemplate.txt");

    //  #region Set Namespace on CDO Collections Extended

    //  spText = spText.Replace("|NAMESPACE|", txtNSCDODO.Text);

    //  #endregion

    //  string variableList = GetCdoCollectionPrivateVariableList();
    //  string propertylist = GetCdoCollectionPropertyList();
    //  string retrieveParams = GetCdoCollectionRetrieveAllParams();
    //  string setPropertiesList = GetCdoCollectionSetList();
    //  string lowerTableName = m_TableName[0].ToString().ToLower() + m_TableName.Substring(1);
    //  string sAuditObjectCode = "//set oldValue object to value as at retrieve. For auditing purposes\n\rcdo|TABLENAME|.OldValue = CloneHelper.Clone(cdo|TABLENAME|) as Cdo|TABLENAME|;";
    //  string sUsingAuditObject = "using Afrs.Prms.Client.Proxy.Helpers;";

    //  if (chkAudit.Checked)
    //  {
    //    spText = spText.Replace("|USINGAUDITOBJECT|", sUsingAuditObject);
    //    spText = spText.Replace("|AUDITOBJECT|", sAuditObjectCode);
    //  }
    //  else
    //  {
    //    spText = spText.Replace("|USINGAUDITOBJECT|", "");
    //    spText = spText.Replace("|AUDITOBJECT|", "");
    //  }

    //  spText = spText.Replace("|VARIABLELIST|", variableList);
    //  spText = spText.Replace("|PROPERTYLIST|", propertylist);
    //  spText = spText.Replace("|RETRIEVEPARAMS|", retrieveParams);
    //  spText = spText.Replace("|SETPROPERTIES|", setPropertiesList);
    //  spText = spText.Replace("|LOWERTABLENAME|", lowerTableName);
    //  spText = spText.Replace("|CWS|", cmbWebService.Text.Trim());

    //  spText = spText.Replace("|WEBSERVICENAME|", cmbWebService.Text.Trim().Replace("Cws", ""));

    //  spText = spText.Replace("|TABLENAME|", m_TableName);

    //  txtCdoExtended.Text = spText;
    //}

    //private string GetCdoCollectionPrivateVariableList()
    //{
    //  string returnString = "";

    //  foreach (DataGridViewRow thisRow in DGTableDetails.Rows)
    //  {
    //    if (thisRow.Cells[0].Value != null)
    //    {
    //      // Do not create for the ID
    //      if (Convert.ToBoolean(thisRow.Cells[5].Value))
    //      {
    //        string columnName = thisRow.Cells[0].Value.ToString().Trim();
    //        string friendlyFieldName = GetFriendlyFieldName(columnName);
    //        string fieldType = thisRow.Cells[2].Value.ToString().Trim();
    //        string defaultValue = ClearDataType(thisRow.Cells[1].Value.ToString().Trim());

    //        string line = "    private " + fieldType + " m_" + friendlyFieldName + " = " + defaultValue + ";\n\r";

    //        returnString = returnString + line;
    //      }
    //    }
    //  }

    //  return (returnString);
    //}

    //private string GetCdoCollectionPropertyList()
    //{
    //  string returnString = "";

    //  foreach (DataGridViewRow thisRow in DGTableDetails.Rows)
    //  {
    //    if (thisRow.Cells[0].Value != null)
    //    {
    //      // Do not create for the ID
    //      if (Convert.ToBoolean(thisRow.Cells[5].Value))
    //      {
    //        string propertyTemplate = GetTemplateText("CdoCollectionPropertyItem.txt");

    //        string columnName = thisRow.Cells[0].Value.ToString().Trim();
    //        string friendlyFieldName = GetFriendlyFieldName(columnName);
    //        string fieldType = GetSystemDataType(thisRow.Cells[1].Value.ToString().Trim());
    //        //fieldType = fieldType.Replace("System", "DbType");
            
    //        propertyTemplate = propertyTemplate.Replace("|FRIENDLYNAME|", friendlyFieldName);
    //        propertyTemplate = propertyTemplate.Replace("|DBTYPE|", fieldType);
    //        propertyTemplate = propertyTemplate.Replace("|FIELDTYPE|", thisRow.Cells[2].Value.ToString().Trim());


    //        returnString = returnString + propertyTemplate + "\n\r";
    //      }
    //    }
    //  }

    //  return (returnString);
    //}

    //private string GetCdoCollectionRetrieveAllParams()
    //{
    //  string returnString = "";

    //  foreach (DataGridViewRow thisRow in DGTableDetails.Rows)
    //  {
    //    if (thisRow.Cells[0].Value != null)
    //    {
    //      // Do not create for the ID
    //      if (Convert.ToBoolean(thisRow.Cells[5].Value))
    //      {
    //        string columnName = thisRow.Cells[0].Value.ToString().Trim();
    //        string friendlyFieldName = GetFriendlyFieldName(columnName);
    //        string variableName = friendlyFieldName;
    //        if (variableName != "Id") variableName = "m_" + variableName;

    //        if (returnString != "") returnString = returnString + ", ";

    //        returnString = returnString + variableName;
    //      }
    //    }
    //  }

    //  return (returnString);
    //}

    //private string GetCdoCollectionSetList()
    //{
    //  string returnString = "";

    //  foreach (DataGridViewRow thisRow in DGTableDetails.Rows)
    //  {
    //    if (thisRow.Cells[0].Value != null)
    //    {
    //      string columnName = thisRow.Cells[0].Value.ToString().Trim();
    //      string friendlyFieldName = GetFriendlyFieldName(columnName);

    //      string lowerTableName = m_TableName[0].ToString().ToLower() + m_TableName.Substring(1);
    //      string line = "\t\t\t\t\tcdo" + m_TableName + "." + friendlyFieldName + " = " + lowerTableName + "." + friendlyFieldName + ";";
    //      returnString = returnString + line + "\n\r";
    //    }
    //  }

    //  return (returnString);
    //}

    //#endregion

    //#endregion

    //#region Unit Test

    //private void CreateUnitTest()
    //{

    //  string spText = "";

    //  spText = GetTemplateText("UnitTestTemplate.txt");

    //  #region Set Namespace on Unit Test

    //  spText = spText.Replace("|NAMESPACE|", txtNSUT.Text);

    //  #endregion

    //  string lowerTableName = m_TableName[0].ToString().ToLower() + m_TableName.Substring(1);

    //  spText = spText.Replace("|SECTION|", txtWebServiceSection.Text.Trim());

    //  spText = spText.Replace("|SMALLTABLENAME|", lowerTableName);

    //  spText = spText.Replace("|TABLENAME|", m_TableName);

    //  spText = spText.Replace("|PARAMLIST|", GetUnitTestVariableList());

    //  spText = spText.Replace("|PRIMARYKEY|", m_PrimaryKeyColName);

    //  spText = spText.Replace("|PRIMARYKEYUPPER|", m_PrimaryKeyColName.ToUpper().Replace("_", ""));

    //  spText = spText.Replace("|CREATEVALUES|", GetUnitTestCreateVariableList(lowerTableName));


    //  txtUnitTest.Text = spText;


    //}

    //private string GetUnitTestVariableList()
    //{

    //  string returnString = "";

    //  foreach (DataGridViewRow thisRow in DGTableDetails.Rows)
    //  {

    //    if (thisRow.Cells[0].Value != null)
    //    {

    //      string columnName = thisRow.Cells[0].Value.ToString().Trim();

    //      string friendlyFieldName = GetFriendlyFieldName(columnName);

    //      string variableName = friendlyFieldName;

    //      if (variableName != "Id")
    //      {
    //        variableName = "m_" + variableName;

    //        string line = "\t\tprivate const " + thisRow.Cells[2].Value.ToString() + " " + columnName.ToUpper().Replace("_", "") + " = ;";

    //        returnString = returnString + line + "\n\r";
    //      }
    //    }

    //  }

    //  return (returnString);

    //}

    //private string GetUnitTestCreateVariableList(string smallTableName)
    //{

    //  string returnString = "";

    //  foreach (DataGridViewRow thisRow in DGTableDetails.Rows)
    //  {

    //    if (thisRow.Cells[0].Value != null)
    //    {

    //      string columnName = thisRow.Cells[0].Value.ToString().Trim();

    //      string friendlyFieldName = GetFriendlyFieldName(columnName);

    //      string variableName = friendlyFieldName;

    //      if (variableName != "Id")
    //      {
    //        variableName = "m_" + variableName;

    //        string line = "\t\t\t" + smallTableName + "." + friendlyFieldName + " = " + columnName.ToUpper().Replace("_", "") + ";";

    //        returnString = returnString + line + "\n\r";
    //      }
    //    }

    //  }

    //  return (returnString);

    //}

    //#endregion

    #endregion

    //#region Save File & Settings Methods

    private void LoadSettings()
    {
      txtDOPath.Text = thisSettings.DOPath;
    //  txtCTPath.Text = thisSettings.CTPath;
    //  txtEALPath.Text = thisSettings.EALPath;
    //  txtEALPartialFileExtension.Text = thisSettings.EALPartialFileExtension;
    //  txtCdoPath.Text = thisSettings.CDOPath;
    //  txtCdoCollectionPath.Text = thisSettings.CDOCollectionPath;
    //  txtWebServicePath.Text = thisSettings.WSFilePath;
      txtStoredProcSavePath.Text = thisSettings.SQLPath;
      txtDBName.Text = thisSettings.DBName;
      txtDBServer.Text = thisSettings.DBServer;
      txtDBUserID.Text = thisSettings.DBUserID;
      txtDBPassword.Text = thisSettings.DBPassword;
    //  txtUnitTestPath.Text = thisSettings.UnitTestPath;
    //  chkWindowsAuth.Checked = thisSettings.UseWindowsAuth;
      txtNSDO.Text = thisSettings.NamespaceDO;
    //  txtNSCT.Text = thisSettings.NamespaceCT;
    //  txtNSEAL.Text = thisSettings.NamespaceEAL;
    //  txtNSWS.Text = thisSettings.NamespaceWS;
    //  txtNSCDODO.Text = thisSettings.NamespaceCDO;
    //  txtNSUT.Text = thisSettings.NamespaceUT;

      //LoadWebServices();
    }

    private void LoadWebServices()
    {
    //  cmbWebService.Items.Clear();
    //  thisSettings = new AppSettings();
    //  foreach (string sItem in thisSettings.WebServices)
    //  {
    //    cmbWebService.Items.Add(sItem);
    //  }
    }

    private int CheckSettings()
    {
      int iResult = 0;

      if (txtDOPath.Text.Length == 0)
        iResult = 1;

      if (txtDBName.Text.Length == 0 || txtDBServer.Text.Length == 0)
        iResult = 2;
      else
        if (!chkWindowsAuth.Checked && txtDBUserID.Text.Length == 0)
          iResult = 2;

      if (txtNSDO.Text.Length == 0)
        iResult = 3;

      return iResult;
    }

    private void SaveSettings()
    {
    //  //AppSettings thisSettings = new AppSettings();
    //  thisSettings.DOPath = txtDOPath.Text;
    //  thisSettings.CTPath = txtCTPath.Text;
    //  thisSettings.EALPath = txtEALPath.Text;
    //  thisSettings.EALPartialFileExtension = txtEALPartialFileExtension.Text;
    //  thisSettings.CDOPath = txtCdoPath.Text;
    //  thisSettings.CDOCollectionPath = txtCdoCollectionPath.Text;
    //  thisSettings.WSFilePath = txtWebServicePath.Text;
    //  thisSettings.SQLPath = txtStoredProcSavePath.Text;
    //  thisSettings.DBName = txtDBName.Text;
    //  thisSettings.DBServer = txtDBServer.Text;
    //  thisSettings.DBUserID = txtDBUserID.Text;
    //  thisSettings.DBPassword = txtDBPassword.Text;
    //  thisSettings.UnitTestPath = txtUnitTestPath.Text.Trim();

    //  thisSettings.Save();
    }

    private void SaveFileSettings()
    {
      //AppSettings thisSettings = new AppSettings();
      thisSettings.DOPath = txtDOPath.Text;
    //  thisSettings.CTPath = txtCTPath.Text;
    //  thisSettings.EALPath = txtEALPath.Text;
    //  thisSettings.EALPartialFileExtension = txtEALPartialFileExtension.Text;
    //  thisSettings.CDOPath = txtCdoPath.Text;
    //  thisSettings.CDOCollectionPath = txtCdoCollectionPath.Text;
    //  thisSettings.WSFilePath = txtWebServicePath.Text;
      thisSettings.SQLPath = txtStoredProcSavePath.Text;
    //  thisSettings.UnitTestPath = txtUnitTestPath.Text.Trim();

      thisSettings.Save();
    }

    private void SaveDBSettings()
    {
    //  //AppSettings thisSettings = new AppSettings();
      thisSettings.DBName = txtDBName.Text;
      thisSettings.DBServer = txtDBServer.Text;
      thisSettings.DBUserID = txtDBUserID.Text;
      thisSettings.DBPassword = txtDBPassword.Text;
      thisSettings.UseWindowsAuth = chkWindowsAuth.Checked;

      thisSettings.Save();
    }

    private void SaveWebServiceSettings()
    {
    //  //AppSettings thisSettings = new AppSettings();
    //  //thisSettings.WebServices = txtDBName.Text;
    //  //thisSettings.DBServer = txtDBServer.Text;
    //  //thisSettings.DBUserID = txtDBUserID.Text;
    //  //thisSettings.DBPassword = txtDBPassword.Text;
    //  //thisSettings.UseWindowsAuth = chkWindowsAuth.Checked;

    //  //thisSettings.Save();
    }

    private void SaveNamespaceSettings()
    {
    //  //AppSettings thisSettings = new AppSettings();
      thisSettings.NamespaceDO = txtNSDO.Text;
    //  thisSettings.NamespaceCT = txtNSCT.Text;
    //  thisSettings.NamespaceEAL = txtNSEAL.Text;
    //  thisSettings.NamespaceWS = txtNSWS.Text;
    //  thisSettings.NamespaceCDO = txtNSCDODO.Text;
    //  thisSettings.NamespaceUT = txtNSUT.Text;

      thisSettings.Save();
    }    

    //private void SaveFiles(string fileName, string fileFolder, string SaveText)
    //{
    //  FileStream fileStream;

    //  // Make sure the directory exists
    //  if (!Directory.Exists(fileFolder)) { Directory.CreateDirectory(fileFolder); }

    //  if (File.Exists(fileFolder + "/" + fileName))
    //  {
    //    if (MessageBox.Show(fileName + " already exists, do you wish to overwrite it?", "File exists", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.No)
    //      return;
    //  }

    //  // Delete the file if it exists
    //  if (File.Exists(fileFolder + "/" + fileName)) { File.Delete(fileFolder + "/" + fileName); }

    //  // Create the required objects
    //  fileStream = new FileStream(fileFolder + "/" + fileName, FileMode.CreateNew);
    //  // Saves the Text via a fileStream created by the OpenFile method.
    //  StreamWriter sw = new StreamWriter(fileStream);

    //  sw.Write(SaveText);
    //  sw.Close();
    //  fileStream.Close();
    //}

    //#endregion

    //#region Reset Methods
    //private void ResetFields(bool bPartialReset)
    //{
    //  if (!bPartialReset)
    //  {
    //    txtTableName.Text = "";
    //    DGTableDetails.Rows.Clear();
    //  }

    //  txtSPName.Text = "";
    //  txtSPCreate.Text = "";
    //  txtSPDelete.Text = "";
    //  txtSPRetrieve.Text = "";
    //  txtSPRetrieveAll.Text = "";
    //  txtSPUpdate.Text = "";

    //  txtDO.Text = "";
    //  txtContract.Text = "";
    //  txtEalBase.Text = "";
    //  txtEalExtended.Text = "";
    //  txtCDO.Text = "";
    //  txtCDOCollection.Text = "";
    //  txtCdoDoExtended.Text = "";
    //  txtCdoExtended.Text = "";
    //  txtWebService.Text = "";
    //  txtUnitTest.Text = "";
    //  btnDOOptions.Text = "Set DO Options";
    //  btnAdd.Text = "Add";
    //  btnAdd.Visible = false;
    //  lblEmbeded.Visible = false;
    //  cmbWebService.SelectedIndex = -1;
    //  DGTableDetails.Enabled = false;
    //  tbMain.Enabled = false;
    //  btnDOOptions.Enabled = false;
    //  txtWebServiceSection.Enabled = false;
    //  cmbWebService.Enabled = false;
    //  btnGenerate.Enabled = false;
    //  btnSave.Enabled = false;
    //  btnAddWebService.Enabled = false;
    //  sEmbededObjectList.Clear();
    //  SystemOptions.Reset();
    //}
    //#endregion

    #endregion


    private void chkAudit_CheckedChanged(object sender, EventArgs e)
    {
        lblEntityID.Visible = chkAudit.Checked;
        txtEntityID.Visible = chkAudit.Checked;
      }

    private void GrdGenerate_CellDirtyStateChanged(object sender, EventArgs e)
    {
      GrdGenerate.CommitEdit(DataGridViewDataErrorContexts.CurrentCellChange);
    }

    private void GrdGenerate_CellValueChanged(object sender, DataGridViewCellEventArgs e)
    {
      if (e.RowIndex >= 0)
      {
        switch (e.ColumnIndex)
        {
          case 0:
            {
              if (bool.Parse((GrdGenerate.Rows[e.RowIndex].Cells[e.ColumnIndex] as DataGridViewCheckBoxCell).Value.ToString()))
              {
                frmSearchColumns loadForm = new frmSearchColumns();
                loadForm.TableColumns = GrdGenerate.Rows[e.RowIndex].Tag as DataTable;
                //loadForm.returnForm = this;
                if (this.m_SelectedTableColumns != null && this.m_SelectedTableColumns.Count > 0)
                  loadForm.SelectedList = this.m_SelectedTableColumns;

                loadForm.ShowDialog();
                if (this.m_DOOptions == null)
                  MessageBox.Show("Error Setting DO Options");

                if (this.m_SelectedTableColumns != null)
                {
                  if (this.m_SelectedTableColumns.Count <= 0)
                  {
                    (GrdGenerate.Rows[e.RowIndex].Cells[e.ColumnIndex] as DataGridViewCheckBoxCell).Value = false;
                  }
                  else
                  {
                    (GrdGenerate.Rows[e.RowIndex].Cells[2] as DataGridViewComboBoxCell).Items.Clear();
                    //test3.Add(this.m_SelectedTableColumns);
                    test2.Add(this.m_DOOptions, test3);
                    test.Add(GrdGenerate.Rows[e.RowIndex].Cells[1].Value.ToString(),test2);

                    //this.m_SelectedObjects.Add( //[iSelectedTablesCount]
                    //(GrdGenerate.Rows[e.RowIndex].Cells[2] as DataGridViewComboBoxCell).Items.Add("[Click for List]");
                    for (int i = 0; i < this.m_SelectedTableColumns.Count; i++)
                    {
                      (GrdGenerate.Rows[e.RowIndex].Cells[2] as DataGridViewComboBoxCell).Items.Add(this.m_SelectedTableColumns[i][0]);
                    }
                    DataGridViewComboBoxCell column = (GrdGenerate.Rows[e.RowIndex].Cells[2] as DataGridViewComboBoxCell);

                  }
                }
                else
                {
                  (GrdGenerate.Rows[e.RowIndex].Cells[e.ColumnIndex] as DataGridViewCheckBoxCell).Value = false;
                }
              }
              else
              {
                //MessageBox.Show("Piel");
              }
              break;
            }
          case 3:
            {
              ////ConstraintCollection constraint = sourceTables.Constraints;
              //DataTable dt = SQLConn.GetSchema("Foreign_Keys");

              if (bool.Parse((GrdGenerate.Rows[e.RowIndex].Cells[e.ColumnIndex] as DataGridViewCheckBoxCell).Value.ToString()))
              {
                if (!bool.Parse((GrdGenerate.Rows[e.RowIndex].Cells[0] as DataGridViewCheckBoxCell).Value.ToString()))
                {
                  MessageBox.Show("You cannot specify embedded objects when the object has not been marked for generation!");
                }
                else
                {
                  frmEmbededObjects loadForm = new frmEmbededObjects();
                  //loadForm.TableColumns = GrdGenerate.Rows[e.RowIndex].Tag as DataTable;
                  loadForm.TableName = GrdGenerate.Rows[e.RowIndex].Cells[1].Value.ToString();
                  //loadForm.returnForm = this;
                  if (this.m_EmbededObjectList != null && this.m_EmbededObjectList.Count > 0)
                    loadForm.SelectedList = this.m_EmbededObjectList;
                  loadForm.ActiveConn = SQLConn;
                  loadForm.ShowDialog();
                  if (this.m_EmbededObjectList.Count <= 0)
                  {
                    (GrdGenerate.Rows[e.RowIndex].Cells[e.ColumnIndex] as DataGridViewCheckBoxCell).Value = false;
                  }
                  else
                  {
                    (GrdGenerate.Rows[e.RowIndex].Cells[4] as DataGridViewComboBoxCell).Items.Clear();
                    
                    //(GrdGenerate.Rows[e.RowIndex].Cells[4] as DataGridViewComboBoxCell).Items.Add("[Click for List]");
                    for (int i = 0; i < this.m_EmbededObjectList.Count; i++)
                    {
                      (GrdGenerate.Rows[e.RowIndex].Cells[4] as DataGridViewComboBoxCell).Items.Add(this.m_EmbededObjectList[i][2]);
                    }
                    (GrdGenerate.Rows[e.RowIndex].Cells[4] as DataGridViewComboBoxCell).Selected = true;
                  }
                }
              }
              else
              {
                //MessageBox.Show("Piel");
              }
              break;
            }
        }
      }
    }

    private void btnPreview_Click(object sender, EventArgs e)
    {
      
      List<List<List<String>>> Settings = new List<List<List<String>>>();
      //List<a> b = new List<a>();

      Dictionary<string,List<List<List<String>>>> SelectionSettings = new Dictionary<string,List<List<List<string>>>>(); 
           
      foreach (DataGridViewRow currentRow in GrdGenerate.Rows)
      {
        //Settings = new List<List<List<string>>>();
        if (bool.Parse((currentRow.Cells[0] as DataGridViewCheckBoxCell).Value.ToString()))
        {
          Settings.Add(this.m_SelectedTableColumns);
          Settings.Add(this.m_EmbededObjectList);
          SelectionSettings.Add(currentRow.Cells[1].Value.ToString(), Settings);
        }
      }
    }
  }
}